﻿Public Class clsCPCK
    Public Function getList(prod_id As String, location As String,
                            time_start As String, time_end As String) As DataTable
        Dim szSql As String = ""
        Dim dt As DataTable = Nothing
        szSql &= "select * from sf.b_warehouse where 1 = 1 "
        If String.IsNullOrEmpty(prod_id) = False Then
            szSql &= " and prod_id like '%" + prod_id + "%'"
        End If
        If String.IsNullOrEmpty(location) = False Then
            szSql &= " and location = '" + location + "'"
        End If
        If String.IsNullOrEmpty(time_start) = False And String.IsNullOrEmpty(time_end) = False Then
            szSql &= " and (into_date >= '" + DateTime.Parse(time_start & " 00:00:00.000") + "'"
            szSql &= " and into_date <= '" + DateTime.Parse(time_end + " 23:59:59.999") + "')"
            szSql &= " or (out_date >= '" + DateTime.Parse(time_start & " 00:00:00.000") + "'"
            szSql &= " and out_date <= '" + DateTime.Parse(time_end + " 23:59:59.999") + "')"
        End If
        Return DBacs.ExecuteSql(szSql, -1)
    End Function

    Public Function getListByStatus(status As String) As DataTable
        Dim szSql As String = "select a.*,b.steel_seal_no,c.prod_spec_id,c.prod_spec_name from sf.b_warehouse a "
        szSql &= "left join sf.b_prod_info b on a.prod_id = b.prod_id "
        szSql &= "left join sf.t_prod_spec c on b.prod_spec_id = c.prod_spec_id "
        szSql &= "where 1 = 1 and a.status = ? "
        Return DBacs.ExecuteSql(szSql, -1, status)
    End Function

    Public Function getListByProdId(prodid As String, status As String) As DataTable
        Dim szSql As String = "select a.*,b.steel_seal_no,c.prod_spec_name from sf.b_warehouse a "
        szSql &= "left join sf.b_prod_info b on a.prod_id = b.prod_id "
        szSql &= "left join sf.t_prod_spec c on b.prod_spec_id = c.prod_spec_id "
        szSql &= " where 1 = 1 "
        szSql &= " and a.prod_id = '" + prodid + "'"
        szSql &= " and a.status = '" + status + "'"
        Return DBacs.ExecuteSql(szSql, -1)
    End Function

    Public Function intoCK(en As entity_cp_ck) As Integer
        Dim sql As String = ""
        Dim rtn As Integer = 0
        DBacs.BeginTransaction()
        Try
            sql = "select prod_id from sf.b_warehouse where prod_id = ? "
            Dim dtw As DataTable = DBacs.ExecuteSql(sql, -1, en.prod_id)
            If dtw.Rows.Count = 0 Then
                sql = "INSERT INTO sf.b_warehouse(factory_id, location, prod_id, into_date, status, into_ope, update_time, area, warehouse_in_code, prod_spec_id, pgd_id)  "
                sql &= "VALUES ( ?, ?, ?, (SELECT CURRENT_TIMESTAMP(6)), ?, ?, (SELECT CURRENT_TIMESTAMP(6)),?,?,?,?)"
                With en
                    rtn = DBacs.ExecuteUpdateSql(sql, -1, .factory_id, .wh_no, .prod_id, .status, .into_ope, .area, .warehouse_in_code, .prod_spec_id, .pgd_id)
                End With
                sql = "update sf.b_prod_info set update_time=(SELECT CURRENT_TIMESTAMP(6)),status= ?,in_st_ope = ? "
                sql &= " where factory_id = ? and prod_id = ?"
                DBacs.ExecuteUpdateSql(sql, -1, "16", en.into_ope, "TS", en.prod_id)
                sql = "select * from sf.b_warehouse_in where warehouse_in_code = ? and prod_spec_id = ? and area = ? and pgd_id = ? and mes_sync_status = ? "
                Dim dt As DataTable = DBacs.ExecuteSql(sql, -1, en.warehouse_in_code, en.prod_spec_id, en.area, en.pgd_id, "0")
                If dt.Rows.Count = 0 Then
                    sql = "INSERT INTO sf.b_warehouse_in(warehouse_in_code, prod_spec_id, area, pgd_id, warehouse_in_count, mes_sync_status)  "
                    sql &= "VALUES ( ?, ?, ?, ?, ?, ?)"
                    rtn = DBacs.ExecuteUpdateSql(sql, -1， en.warehouse_in_code, en.prod_spec_id, en.area, en.pgd_id, 1, "0")
                Else
                    sql = "update sf.b_warehouse_in set warehouse_in_count = warehouse_in_count + 1 where warehouse_in_code = ? and prod_spec_id = ? and area = ? and pgd_id = ? and mes_sync_status = ? "
                    DBacs.ExecuteUpdateSql(sql, -1, en.warehouse_in_code, en.prod_spec_id, en.area, en.pgd_id, "0")
                End If
                sql = "select * from sf.b_pgd_nc where 1 = 1 and b_pgd_mes_sys_id = ? and procedure_name = ? "
                Dim dtnc As DataTable = DBacs.ExecuteSql(sql, -1, en.pgd_id, "喷漆")
                If dtnc.Rows.Count > 0 Then
                    sql = "select * from sf.b_pgd_nc_workreport where prod_id = ? and procedure_name = ? "
                    Dim dtws As DataTable = DBacs.ExecuteSql(sql, -1, en.prod_id, "力学")
                    If dtws.Rows.Count > 0 Then
                        If dtws.Rows(0)("is_partial_area").ToString().Equals("2") Then
                            sql = "INSERT INTO sf.b_pgd_nc_workreport(b_pgd_mes_sys_id, b_pgd_mes_details_sys_id, oper_date, mes_project_code, mes_project_name, prod_spec_id, prod_spec_name, oper_count, procedure_name,warehouse,is_partial_area,create_time,prod_id) "
                            sql &= "VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , (SELECT CURRENT_TIMESTAMP(6)),?); "
                            rtn = DBacs.ExecuteUpdateSql(sql, -1, en.pgd_id, dtnc.Rows(0)("b_pgd_mes_details_sys_id"), dtnc.Rows(0)("oper_date"), dtnc.Rows(0)("mes_project_code"),
                                                     dtnc.Rows(0)("mes_project_name"), dtnc.Rows(0)("prod_spec_id"), dtnc.Rows(0)("prod_spec_name"),
                                                     "1", dtnc.Rows(0)("procedure_name"), en.area_name, dtws.Rows(0)("is_partial_area"), en.prod_id)
                        End If
                        If dtws.Rows(0)("is_partial_area").ToString().Equals("1") Then
                            sql = "INSERT INTO sf.b_pgd_nc_workreport(b_pgd_mes_sys_id, b_pgd_mes_details_sys_id, oper_date, mes_project_code, mes_project_name, prod_spec_id, prod_spec_name, oper_count, procedure_name,warehouse,is_partial_area,prod_spec_id_partial_area_lx,prod_spec_id_lx,create_time,prod_id) "
                            sql &= "VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , (SELECT CURRENT_TIMESTAMP(6)),?); "
                            rtn = DBacs.ExecuteUpdateSql(sql, -1, en.pgd_id, dtnc.Rows(0)("b_pgd_mes_details_sys_id"), dtnc.Rows(0)("oper_date"), dtnc.Rows(0)("mes_project_code"),
                                                     dtnc.Rows(0)("mes_project_name"), dtnc.Rows(0)("prod_spec_id"), dtnc.Rows(0)("prod_spec_name"),
                                                     "1", dtnc.Rows(0)("procedure_name"), en.area_name, dtws.Rows(0)("is_partial_area"), dtws.Rows(0)("prod_spec_id_partial_area_lx"), dtws.Rows(0)("prod_spec_id_lx"), en.prod_id)
                        End If
                    End If
                End If
                'Dim opn As Integer = Integer.Parse(Double.Parse("1"))
                'sql = "select prod_id,pgd_id,b_pgd_yzz_details_sys_id from sf.b_prod_info "
                'sql &= " where factory_id = ? and prod_id = ?"
                'Dim dt As DataTable = DBacs.ExecuteSql(sql, -1, "TS", en.prod_id)
                'sql = "INSERT INTO sf.b_pgd_mes_workreport(oper_type, b_pgd_mes_sys_id,oper_num, operator, update_time, prod_id, workshop_id, mes_sync_result)  "
                'sql &= "VALUES ( ?, ?, ?, ?, (SELECT CURRENT_TIMESTAMP(6)), ?, ?, ?)"
                'rtn = DBacs.ExecuteUpdateSql(sql, -1， "ok", dt.Rows(0)("pgd_id"), opn, en.into_ope, en.prod_id, "6", String.Empty)
                DBacs.CommitTransaction()
            End If
        Catch ex As Exception
            DBacs.RollbackTransaction()
            rtn = -2
        End Try
        Return rtn
    End Function

    Public Function outCK(prod_ids As String, usr As String) As Integer
        Dim sql As String = ""
        Dim rtn As Integer
        DBacs.BeginTransaction()
        Try
            sql &= " update sf.b_warehouse SET out_date = (SELECT CURRENT_TIMESTAMP(6)), out_ope = ? , status = ? "
            sql &= " where factory_id = ? and prod_id in ("
            sql &= prod_ids
            sql &= ") "
            rtn = DBacs.ExecuteUpdateSql(sql, -1, usr, "1", "TS")
            sql = String.Empty
            sql &= "update sf.b_prod_info set update_time=(SELECT CURRENT_TIMESTAMP(6)),status = ? "
            sql &= " where factory_id = ? and prod_id in ("
            sql &= prod_ids
            sql &= ") "
            rtn = DBacs.ExecuteUpdateSql(sql, -1, "17", "TS")
            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
            rtn = -2
        End Try
        Return rtn
    End Function

End Class
